USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_GetByStatus]    Script Date: 05/25/2011 06:55:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [classified].[ClassifiedAds_GetByStatus]
	@ResourceID bigint,
	@AdStatus tinyint,
	@StartIndex int,
	@AdsOnPage int
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @RowCnt int;

		SELECT COUNT(*) as cnt FROM classified.ClassifiedAds
		WHERE ResourceID = @ResourceID AND Status = @AdStatus
		
		SET @RowCnt = @StartIndex+@AdsOnPage;
		
		SET ROWCOUNT @RowCnt;

		WITH AdsOrdered AS (
			SELECT AdID, CategoryID, [PostTime],[Title], ROW_NUMBER() OVER (ORDER BY PostTime) as rn
				FROM classified.ClassifiedAds
			WHERE ResourceID = @ResourceID AND [Status] = @AdStatus
		) SELECT AdID, CategoryID, [PostTime],[Title] FROM AdsOrdered WHERE rn>@StartIndex AND rn<=@RowCnt

	SET ROWCOUNT 0;

END






GO


